﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using Hyweb.Data.UI.Agent;
using ScriptsGenerator.Manager;
using ScriptsGenerator.Helper;

namespace ScriptDumper.Manager
{
    public class SqlScriptManager
    {
        private static SqlScriptManager _instance = new SqlScriptManager();

        private const string BATCH_MODULE_PREFIX = "B";
        private const string UI_MODULE_PREFIX = "U";
        private const string RREPORT_MODULE_PREFIX = "R";
        private const string ONLINE_MODULE_PREFIX = "O";

        public static SqlScriptManager GetInstance()
        {
            return _instance;
        }

        private string GetSqlScriptInner(DbTable dbTable)
        {
            DbColumn[] columns = dbTable.Columns;

            StringBuilder sb = new StringBuilder();

            string fields = string.Empty;
            foreach (DbColumn column in columns)
            {
                if (column.PkSequence != 0)
                {
                    column.Required = true;
                }

                fields += string.Format("  {0, -30}  {1} {2} {3} {4}," + Environment.NewLine, 
                    column.Name,
                    GetDbType(column.ColumnType), 
                    GetLength(column),
                    GetDefault(column),
                    column.Required ? "NOT NULL" : "");
            }

            if (dbTable.ContainsLog)
            {
                fields += AppendLogFields();
            }

            fields += AppendDefaultsields();

            return fields.TrimEnd('\r', '\n', ',');
        }

        private string AppendDefaultsields()
        {
            string fields = string.Empty;

            fields += @"  CREATE_ID                       NVARCHAR (12),
  CREATE_DATETIME                 DATETIME,
  UPDATE_ID                       NVARCHAR (12),
  UPDATE_DATETIME                 DATETIME,
";

            return fields;
        }

        private string AppendLogFields()
        {
            string fields = string.Empty;

            fields += @"  CONFIRM_ID                      NVARCHAR (12),
  CONFIRM_DATETIME                DATETIME,
";

            return fields;
        }

        private string GetDefault(DbColumn column)
        {
            string defaultValue = "";

            if ("N".Equals(column.ColumnType))
            {
                defaultValue += "DEFAULT 0";
            }
            else
            {
                if (!string.IsNullOrEmpty(column.DefaultValue))
                    defaultValue += "DEFAULT '" + column.DefaultValue + "'";
            }

            return defaultValue;
        }

        private string GetLength(Column column)
        {
            if (0 == column.MaxLength)
                return string.Empty;

            string length = Convert.ToString(column.MaxLength);

            if ("N".Equals(column.ColumnType))
            {
                if (column.MinLength == 0)
                {
                    length = string.Format("({0})", length);
                }
                else
                {
                    length = string.Format("({0}, {1})", column.MaxLength, column.MinLength);
                }
            }
            else if ("INT".Equals(column.ColumnType.ToUpper()) ||
                     "TX".Equals(column.ColumnType.ToUpper()) ||
                     "BIT".Equals(column.ColumnType.ToUpper()))
            {
                length = "";
            }
            else
            {
                length = string.Format("({0})", length);
            }

            return length;
        }

        private string GetDbType(string type)
        {
            switch (type)
            {
                case "VX":
                    return "NVARCHAR";
                case "DT":
                    return "DATETIME";
                case "TX":
                    return "NTEXT";
                case "N":
                    return "NUMERIC";
                case "INT":
                    return "INT";
                case "SINT":
                    return "SMALLINT";
                case "BIT":
                    return "BIT";
                case "TS":
                    return "TIMESTAMP";
            }

            return type;
        }

        private const string TABLE_TEMPLATE = @"
IF OBJECT_ID('{0}') IS NOT NULL DROP TABLE {0}
GO

CREATE TABLE {0}
(
{1}
) 
{2};
";

        internal string GetSqlScript(DbTable dbTable)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(TABLE_TEMPLATE,
                            dbTable.TableName,
                            GetSqlScriptInner(dbTable),
                            GetPKeyScripts(dbTable));

            if (dbTable.ContainsLog)
            {
                sb.Append(GetLogSqlScript(dbTable));
                sb.Append(GetHistorySqlScript(dbTable));
            }

            return sb.ToString();
        }

        private string GetPKeyScripts(DbTable dbTable)
        {
            string scripts = string.Empty;

            if (!string.IsNullOrEmpty(GetPKey(dbTable.Columns)))
            {
                scripts += string.Format(@"ALTER TABLE {0} ADD CONSTRAINT PK_{2} PRIMARY KEY ({1})", 
                  dbTable.TableName, 
                  GetPKey(dbTable.Columns),
                  dbTable.ShortTableName);
            }

            return scripts;
        }

        #region History Table
        private const string HISTORY_TABLE_TEMPLATE = @"
IF OBJECT_ID('{0}_HIST') IS NOT NULL DROP TABLE {0}_HIST
GO

CREATE TABLE {0}_HIST
(
{2}
  LOG_ID                          INT NOT NULL,
  HIST_ID                         INT NOT NULL,
  MAINTAIN_TYPE                   NVARCHAR(1),
  CREATE_ID                       NVARCHAR(12),
  CREATE_DATETIME                 DATETIME,
) 
ALTER TABLE {0}_HIST ADD CONSTRAINT PK_{3}_HIST PRIMARY KEY ({1}, LOG_ID, HIST_ID);
";

        private string GetHistorySqlScript(DbTable dbTable)
        {
            string fields = string.Empty;

            string pkfields = GetPKeyFields(dbTable);

            fields = string.Format(HISTORY_TABLE_TEMPLATE,
                                    dbTable.TableName,
                                   GetPKey(dbTable.Columns),
                                   GetColumn(dbTable),
                                   dbTable.ShortTableName);
            return fields;
        } 
        #endregion

        #region Log Table

        private const string LOG_TABLE_TEMPLATE = @"
IF OBJECT_ID('{0}_LOG') IS NOT NULL DROP TABLE {0}_LOG
GO

CREATE TABLE {0}_LOG
(
{2}
  LOG_ID                          INT NOT NULL,
  CONFIRM_ROLE                    NVARCHAR(12),
  MAINTAIN_TYPE                   NVARCHAR(1),
  IS_CONFIRM                      BIT,
  CONFIRM_TYPE                    DATETIME,
  CONFIRM_ID                      NVARCHAR(12),
  CONFIRM_DATETIME                DATETIME,
  CREATE_ID                       NVARCHAR(12),
  CREATE_DATETIME                 DATETIME,
  UPDATE_ID                       NVARCHAR(12),
  UPDATE_DATETIME                 DATETIME
) 
ALTER TABLE {0}_LOG ADD CONSTRAINT PK_{3}_LOG PRIMARY KEY ({1}, LOG_ID);
";

        private string GetLogSqlScript(DbTable dbTable)
        {
            string fields = string.Empty;

            string pkfields = GetPKeyFields(dbTable);

            fields = string.Format(LOG_TABLE_TEMPLATE,
                                   dbTable.TableName,
                                   GetPKey(dbTable.Columns),
                                   GetColumn(dbTable),
                                   dbTable.ShortTableName);
            return fields;
        }

        #endregion

        private string GetColumn(DbTable dbTable)
        {
            string fields = string.Empty;
            foreach (DbColumn column in dbTable.Columns)
            {
                fields += GetColumnInner(column, "," + Environment.NewLine);

            }

            return fields.TrimEnd('\r', '\n');
        }

        private string GetColumnInner(DbColumn column, string endString)
        {
            return string.Format("  {0, -30}  {1} {2} {3} {4}" + endString,
                                    column.Name,
                                    GetDbType(column.ColumnType),
                                    GetLength(column),
                                    GetDefault(column),
                                    column.Required ? "NOT NULL" : "");
        }

        private string GetPKeyFields(DbTable dbTable)
        {
            string fields = string.Empty;
            foreach (DbColumn column in dbTable.Columns)
            {
                if (column.PkSequence != 0)
                {
                    fields += GetColumnInner(column, "," + Environment.NewLine);
                }
            }

            return fields.TrimEnd(',');
        }

        private string GetPKey(DbColumn[] dbColumns)
        {
            DbColumn[] columns = new DbColumn[dbColumns.Length];

            Array.Copy(dbColumns, columns, columns.Length);

            Array.Sort(columns, new DbColumnComparer());

            List<string> fields = new List<string>();
            foreach (DbColumn column in columns)
            {
                if (column.PkSequence != 0)
                    fields.Add(column.Name);
            }

            return string.Join(",", (fields.ToArray() as string[]));
        }

        #region DbColumnComparer
        private class DbColumnComparer : IComparer
        {
            #region IComparer 成員

            public int Compare(object x, object y)
            {
                DbColumn xx = x as DbColumn;
                DbColumn yy = x as DbColumn;

                return xx.PkSequence.CompareTo(yy.PkSequence);  
            }

            #endregion
        }
        #endregion
    
        internal string GetPatchedSqlScript(DbTable table, Amendment amendment)
        {
            StringBuilder sb = new StringBuilder();

            switch (amendment.ChangeAction)
            {
                case ScriptsGenerator.Helper.ChangeAction.Add:
                    sb.Append(GetAddPatchedSqlScript(table, amendment) + Environment.NewLine);
                    break;
                case ScriptsGenerator.Helper.ChangeAction.Change:
                    sb.Append(GetChangePatchedSqlScript(table, amendment) + Environment.NewLine);
                    break;
                case ScriptsGenerator.Helper.ChangeAction.Delete:
                    sb.Append(GetDeletePatchedSqlScript(table, amendment) + Environment.NewLine);
                    break;
                default:
                    break;
            }

            if (amendment.PKeySequence > 0)
            {
                sb.Append(GetDropPKeyScripts(table) + Environment.NewLine);
                sb.Append(GetPKeyScripts(table) + Environment.NewLine);
            }

            return sb.ToString();
        }

        private string GetChangePatchedSqlScript(DbTable table, Amendment amendment)
        {
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(amendment.Field))
            {
                sb.AppendFormat(SqlScriptsConstant.EXEC_SP_DROPDEFAULT + Environment.NewLine, amendment.Table, amendment.Field);
                sb.AppendFormat(SqlScriptsConstant.ALTER_TABLE_ALTER_COLUMN, amendment.Table, GetColumnInner(table.DbColumns[amendment.Field], string.Empty));
            }

            return sb.ToString();
        }

        private string GetDeletePatchedSqlScript(DbTable table, Amendment amendment)
        {
            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(amendment.Field))
            {
                sb.AppendFormat(SqlScriptsConstant.DROP_TABLE, table.TableName);
            }
            else
            {
                sb.AppendFormat(SqlScriptsConstant.ALERT_TABLE_DROP_COLUMN, amendment.Table, amendment.Field, string.Empty);
            }

            return sb.ToString();
        }

        private string GetAddPatchedSqlScript(DbTable table, Amendment amendment)
        {
            StringBuilder sb = new StringBuilder();
            if (string.IsNullOrEmpty(amendment.Field))
            {
                sb.Append(GetSqlScript(table));
            }
            else
            {
                sb.AppendFormat(SqlScriptsConstant.ALTER_TABLE_ADD_COLUMN, amendment.Table , GetColumnInner(table.DbColumns[amendment.Field], string.Empty));
            }

            return sb.ToString();
        }

        private string GetDropPKeyScripts(DbTable table)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(SqlScriptsConstant.ALERT_TABLE_DROP_PKEY_CONSTRIANTS, table.TableName, table.ShortTableName);
            return sb.ToString();
        }
    }
}
